﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using Antu.WebControls;
using Antu.Core.Components;
using Antu.Core.Services;
using System.Web.UI.WebControls;
using EluZSProvider;
using System.Web;
using System.Data;
using Antu.Business.DmpExcel;

namespace WebSys.UI.Control.ZhengShou
{
    public class ZS_ProjectGSListControl : MPBasicTemplate
    {
        EluZSDataProvider _EluZSDataProvider = (EluZSDataProvider)ProviderService.ProviderServiceMange.GetProvider("EluZSProvider");
        public string userid = string.Empty;
  
        MPRepeater MPRepeaterList;
        Button cx,fh;
        TextBox xxh, fwzl, bzsr, czr;
        DropDownList 排序;
        Button Button3;
        protected override void OnLoad(EventArgs e)
        {
            if (HttpContext.Current.Session["UserID"] == null)
            {
                Antu.Utilities.UtilitiesPage.AlertDialogClear("登录超时，请重新登录！", "window.parent.location.href('../login.aspx');");
            }
            else
            {
                userid = HttpContext.Current.Session["UserID"].ToString();
            }
            base.OnLoad(e);
            InitControl();
            if (!base.Page.IsPostBack)
            {
                Bind();
            }

        }

        protected void Bind()
        {
            string sqlwhere = string.Empty;
            string fw = fwzl.Text;
            if (!string.IsNullOrEmpty(fw))
            {
                sqlwhere += " and h.房屋坐落 like '%" + fw + "%'";
            }
            string bz = bzsr.Text;
            if (!string.IsNullOrEmpty(bz))
            {
                sqlwhere += " and h.被征收人 like '%" + bz + "%'";
            }
            if (排序.SelectedValue == "协议号")
            {
                sqlwhere += " Order By h.协议号 Desc";
            }
            if (排序.SelectedValue == "备案时间")
            {
                sqlwhere += " Order By h.备案时间 Desc";
            }


            string sql = @"Select * From (Select (h.Zsjdh || h.Textbox1) 协议号,
       h.Textbox12 房屋坐落,
       Textbox6 被征收人,
       Textbox7 承租人,
       h.Textbox73 建筑面积,
       To_Number(Nvl(Replace(Textbox85, '\', ''), 0)) 土地面积,
       '' 未登记房屋认定面积,
       To_Number(Nvl(Replace(Textbox19, '\', ''), 0)) 房屋货币补偿金额,
       To_Number(Nvl(Replace(Textbox24, '\', ''), 0)) 搬迁补助费,
       To_Number(Nvl(Replace(Textbox25, '\', ''), 0)) 过渡补助费,
       To_Number(Nvl(Replace(Textbox26, '\', ''), 0)) 管道煤气补助费,
       To_Number(Nvl(Replace(Textbox27, '\', ''), 0)) 固定电话补助费,
       To_Number(Nvl(Replace(Textbox28, '\', ''), 0)) 有线电视拆移补助费,
       To_Number(Nvl(Replace(Textbox29, '\', ''), 0)) 空调拆装补助费,
       To_Number(Nvl(Replace(Textbox30, '\', ''), 0)) 宽带网拆除补助费,
       To_Number(Nvl(Replace(Textbox31, '\', ''), 0)) 太阳能热水器拆移补助费,
       To_Number(Nvl(Replace(Textbox32, '\', ''), 0)) 电增容补助费,
       Nvl(Replace(Textbox125, '\', ''), 0) 附属物补助费,
       Nvl(Replace(Textbox126, '\', ''), 0) 装修补助费,
       To_Number(Nvl(Replace(Textbox34, '\', ''), 0)) 搬家奖励费,
       To_Number(Nvl(Replace(Textbox43, '\', ''), 0)) 营业用房补差款,
       To_Number(Nvl(Replace(Textbox33, '\', ''), 0) +
                 Nvl(Replace(Textbox122, '\', ''), 0)) 其它,
       (To_Number(Nvl(Replace(Textbox19, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox24, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox25, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox26, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox27, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox28, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox29, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox30, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox31, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox32, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox33, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox34, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox122, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox125, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox126, '\', ''), 0))) 合计,
        Nvl(Replace(补充过渡费, '\', ''), 0) 提前搬家过渡费,
                 Nvl(Replace(补充特殊困难, '\', ''), 0) 特殊困难补助费,
                 Nvl(Replace(补充住改非, '\', ''), 0) 住改非补差费,
                 Nvl(Replace(补充其它, '\', ''), 0) 其他,
                 (To_Number(Nvl(Replace(补充过渡费, '\', ''), 0))+
        To_Number(Nvl(Replace(补充特殊困难, '\', ''), 0))+
        To_Number(Nvl(Replace(补充住改非, '\', ''), 0))+
        To_Number(Nvl(Replace(补充其它, '\', ''), 0))) 小计,
        h.备案时间 备案时间,
        (Case When 经济适用房面积1='40' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经40,
        (Case When 经济适用房面积1='50' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经50,
        (Case When 经济适用房面积1='60' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经60,
        (Case When 经济适用房面积1='70' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经70,
        (Case When 补充产权调换房面积1='45' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='45' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='45' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产45,
        (Case When 补充产权调换房面积1='55' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='55' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='55' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产55,
        (Case When 补充产权调换房面积1='65' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='65' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='65' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产65,
        (Case When 补充产权调换房面积1='75' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='75' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='75' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产75,
        (Case When 补充产权调换房面积1='85' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='85' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='85' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产85,
to_char(nvl(To_Number(Nvl(Replace(补充货币补偿金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员1封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员2封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员3封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员4封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员5封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员6封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员7封闭金额, '\', ''), 0)),0)) 封闭资金合计,
h.id id
  From Zs_Hbhxy h  Where h.Personid In (Select Id From Zs_Zsperson Where Pid = '" + Page.Request["Id"] + @"')
 And h.pnid In (Select Id From zs_prono Where pid='" + Page.Request["Id"] + @"')
 and h.提交备案 = '1'
Union All
Select (h.Zsjdh || h.Textbox2) 协议号,
       h.Textbox13 房屋坐落,
       Textbox7 被征收人,
       Textbox8 承租人,
       h.Textbox108 建筑面积,
       To_Number(Nvl(Replace(Textbox120, '\', ''), 0)) 土地面积,
       '' 未登记房屋认定面积,
       To_Number(Nvl(Replace(Textbox21, '\', ''), 0)) 房屋货币补偿金额,
       To_Number(Nvl(Replace(Textbox26, '\', ''), 0)) 搬迁补助费,
       To_Number(Nvl(Replace(Textbox27, '\', ''), 0)) 过渡补助费,
       To_Number(Nvl(Replace(Textbox28, '\', ''), 0)) 管道煤气补助费,
       To_Number(Nvl(Replace(Textbox29, '\', ''), 0)) 固定电话补助费,
       To_Number(Nvl(Replace(Textbox30, '\', ''), 0)) 有线电视拆移补助费,
       To_Number(Nvl(Replace(Textbox31, '\', ''), 0)) 空调拆装补助费,
       To_Number(Nvl(Replace(Textbox32, '\', ''), 0)) 宽带网拆除补助费,
       To_Number(Nvl(Replace(Textbox33, '\', ''), 0)) 太阳能热水器拆移补助费,
       To_Number(Nvl(Replace(Textbox34, '\', ''), 0)) 电增容补助费,
       '' 附属物补助费,
       '' 装修补助费,
       To_Number(Nvl(Replace(Textbox166, '\', ''), 0)) 搬家奖励费,
       To_Number(Nvl(Replace(Textbox44, '\', ''), 0)) 营业用房补差款,
       To_Number(Nvl(Replace(Textbox35, '\', ''), 0) +
                 Nvl(Replace(Textbox167, '\', ''), 0)) 其它,
       (To_Number(Nvl(Replace(Textbox21, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox26, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox27, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox28, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox29, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox30, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox31, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox32, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox33, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox34, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox166, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox35, '\', ''), 0))+
       To_Number(Nvl(Replace(Textbox167, '\', ''), 0))) 合计,
                 '0' 提前搬家过渡费, 
                 '0' 特殊困难补助费,
                 '0' 住改非补差费,
                 '0' 其他,
                 0 小计,      
        sysdate 备案时间,
        '' 经40,
        '' 经50,
        '' 经60,
        '' 经70,
        '' 产45,
        '' 产55,
        '' 产65,
        '' 产75,
        '' 产85,'' 封闭资金合计 ,
        h.id id
  From Zs_Cqzhxy h
 Where h.Fid In
       (Select Id
          From Zs_Proloupan
         Where Pid In
               (Select Id
                  From Zs_Project
                 Where Id = '" + Page.Request["Id"]+ @"'))
                 and h.提交备案 = '1') h
                 where 1=1 " + sqlwhere + " ";
           
            DataTable dt = _EluZSDataProvider.GetTable(sql);
            if (dt.Rows.Count > 0)
            {
                MPRepeaterList.DataSource = dt;
                MPRepeaterList.DataBind();
            }
        }

        
        protected void InitControl()
        {
            cx = (Button)FindControl("cx");
            cx.Click += new EventHandler(cx_Click);
            
            MPRepeaterList = (MPRepeater)FindControl("MPRepeaterList");
            xxh = (TextBox)FindControl("xxh"); 
            fwzl = (TextBox)FindControl("fwzl"); 
            bzsr = (TextBox)FindControl("bzsr");
            fh = (Button)FindControl("fh");
            fh.Click += new EventHandler(fh_Click);
            排序 = (DropDownList)FindControl("排序");
            Button3 = (Button)FindControl("Button3");
            Button3.Click += new EventHandler(Button3_Click);

        }
        
        protected void Button3_Click(object sender, EventArgs e)
        {
            string sqlwhere = string.Empty;
            string fw = fwzl.Text;
            if (!string.IsNullOrEmpty(fw))
            {
                sqlwhere += " and h.房屋坐落 like '%" + fw + "%'";
            }
            string bz = bzsr.Text;
            if (!string.IsNullOrEmpty(bz))
            {
                sqlwhere += " and h.被征收人 like '%" + bz + "%'";
            }
            if (排序.SelectedValue == "协议号")
            {
                sqlwhere += " Order By h.协议号 Desc";
            }
            if (排序.SelectedValue == "备案时间")
            {
                sqlwhere += " Order By h.备案时间 Desc";
            }


            string sql = @"Select * From (Select (h.Zsjdh || h.Textbox1) 协议号,
       h.Textbox12 房屋坐落,
       Textbox6 被征收人,
       Textbox7 承租人,
       h.Textbox73 建筑面积,
       To_Number(Nvl(Replace(Textbox85, '\', ''), 0)) 土地面积,
       '' 未登记房屋认定面积,
       To_Number(Nvl(Replace(Textbox19, '\', ''), 0)) 房屋货币补偿金额,
       To_Number(Nvl(Replace(Textbox24, '\', ''), 0)) 搬迁补助费,
       To_Number(Nvl(Replace(Textbox25, '\', ''), 0)) 过渡补助费,
       To_Number(Nvl(Replace(Textbox26, '\', ''), 0)) 管道煤气补助费,
       To_Number(Nvl(Replace(Textbox27, '\', ''), 0)) 固定电话补助费,
       To_Number(Nvl(Replace(Textbox28, '\', ''), 0)) 有线电视拆移补助费,
       To_Number(Nvl(Replace(Textbox29, '\', ''), 0)) 空调拆装补助费,
       To_Number(Nvl(Replace(Textbox30, '\', ''), 0)) 宽带网拆除补助费,
       To_Number(Nvl(Replace(Textbox31, '\', ''), 0)) 太阳能热水器拆移补助费,
       To_Number(Nvl(Replace(Textbox32, '\', ''), 0)) 电增容补助费,
       Nvl(Replace(Textbox125, '\', ''), 0) 附属物补助费,
       Nvl(Replace(Textbox126, '\', ''), 0) 装修补助费,
       To_Number(Nvl(Replace(Textbox34, '\', ''), 0)) 搬家奖励费,
       To_Number(Nvl(Replace(Textbox43, '\', ''), 0)) 营业用房补差款,
       To_Number(Nvl(Replace(Textbox33, '\', ''), 0) +
                 Nvl(Replace(Textbox122, '\', ''), 0)) 其它,
       To_Number(Nvl(Replace(Textbox37, '\', ''), 0) +
                 Nvl(Replace(Textbox40, '\', ''), 0)) 协议合计,
                 Nvl(Replace(补充过渡费, '\', ''), 0) 提前搬家过渡费,
                 Nvl(Replace(补充特殊困难, '\', ''), 0) 特殊困难补助费,
                 Nvl(Replace(补充住改非, '\', ''), 0) 住改非补差费,
                 Nvl(Replace(补充其它, '\', ''), 0) 其他,
                 to_char(To_Number(Nvl(Replace(补充过渡费, '\', ''), 0))+
        To_Number(Nvl(Replace(补充特殊困难, '\', ''), 0))+
        To_Number(Nvl(Replace(补充住改非, '\', ''), 0))+
        To_Number(Nvl(Replace(补充其它, '\', ''), 0))) 补偿协议小计,
 h.备案时间 备案时间,
  (Case When 经济适用房面积1='40' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经适房面积40,
        (Case When 经济适用房面积1='50' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经适房面积50,
        (Case When 经济适用房面积1='60' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经适房面积60,
        (Case When 经济适用房面积1='70' Then Nvl(Replace(经济适用房套数1, '\', ''), 0) Else '0' End) 经适房面积70,
        (Case When 补充产权调换房面积1='45' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='45' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='45' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产权面积45,
        (Case When 补充产权调换房面积1='55' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='55' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='55' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产权面积55,
        (Case When 补充产权调换房面积1='65' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='65' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='65' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产权面积65,
        (Case When 补充产权调换房面积1='75' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='75' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='75' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产权面积75,
        (Case When 补充产权调换房面积1='85' Then Nvl(Replace(补充产权调换房套数1, '\', ''), 0)
              When 补充产权调换房面积2='85' Then Nvl(Replace(补充产权调换房套数2, '\', ''), 0)
              When 补充产权调换房面积3='85' Then Nvl(Replace(补充产权调换房套数3, '\', ''), 0) Else '0' End) 产权面积85,
to_char(nvl(To_Number(Nvl(Replace(补充货币补偿金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员1封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员2封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员3封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员4封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员5封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员6封闭金额, '\', ''), 0))+
To_Number(Nvl(Replace(补充人员7封闭金额, '\', ''), 0)),0)) 封闭资金合计 
  From Zs_Hbhxy h Where h.Personid In (Select Id From Zs_Zsperson Where Pid = '" + Page.Request["Id"] + @"')
 And h.pnid In (Select Id From zs_prono Where pid='" + Page.Request["Id"] + @"')
and h.提交备案 = '1'
Union All
Select (h.Zsjdh || h.Textbox2) 协议号,
       h.Textbox13 房屋坐落,
       Textbox7 被征收人,
       Textbox8 承租人,
       h.Textbox108 建筑面积,
       To_Number(Nvl(Replace(Textbox120, '\', ''), 0)) 土地面积,
       '' 未登记房屋认定面积,
       To_Number(Nvl(Replace(Textbox21, '\', ''), 0)) 房屋货币补偿金额,
       To_Number(Nvl(Replace(Textbox26, '\', ''), 0)) 搬迁补助费,
       To_Number(Nvl(Replace(Textbox27, '\', ''), 0)) 过渡补助费,
       To_Number(Nvl(Replace(Textbox28, '\', ''), 0)) 管道煤气补助费,
       To_Number(Nvl(Replace(Textbox29, '\', ''), 0)) 固定电话补助费,
       To_Number(Nvl(Replace(Textbox30, '\', ''), 0)) 有线电视拆移补助费,
       To_Number(Nvl(Replace(Textbox31, '\', ''), 0)) 空调拆装补助费,
       To_Number(Nvl(Replace(Textbox32, '\', ''), 0)) 宽带网拆除补助费,
       To_Number(Nvl(Replace(Textbox33, '\', ''), 0)) 太阳能热水器拆移补助费,
       To_Number(Nvl(Replace(Textbox34, '\', ''), 0)) 电增容补助费,
       '' 附属物补助费,
       '' 装修补助费,
       To_Number(Nvl(Replace(Textbox166, '\', ''), 0)) 搬家奖励费,
       To_Number(Nvl(Replace(Textbox44, '\', ''), 0)) 营业用房补差款,
       To_Number(Nvl(Replace(Textbox35, '\', ''), 0) +
                 Nvl(Replace(Textbox167, '\', ''), 0)) 其它,
       To_Number(Nvl(Replace(Textbox49, '\', ''), 0) +
                 Nvl(Replace(Textbox52, '\', ''), 0)) 协议合计,
                 '0' 提前搬家过渡费, 
                 '0' 特殊困难补助费,
                 '0' 住改非补差费,
                 '0' 其他,
                 '0' 补偿协议小计,      
 sysdate 备案时间,
        '' 经适房面积40,
        '' 经适房面积50,
        '' 经适房面积60,
        '' 经适房面积70,
        '' 产权面积45,
        '' 产权面积55,
        '' 产权面积65,
        '' 产权面积75,
        '' 产权面积85,'' 封闭资金合计
  From Zs_Cqzhxy h
 Where h.Fid In
       (Select Id
          From Zs_Proloupan
         Where Pid In
               (Select Id
                  From Zs_Project
                 Where Id = '" + Page.Request["Id"] + @"'))
                and h.提交备案 = '1'
) h
                 
                 where 1=1 " + sqlwhere + "  ";
            DataTable dt = _EluZSDataProvider.GetTable(sql);
            ExcelStruct excStruct = new ExcelStruct();
            excStruct.DataTableTemp = dt;
            excStruct.PageSource = this.Page;
            excStruct.Flag = "0";
            excStruct.TableName = "项目补偿信息公示";
            excStruct.HiddenField = "备案时间";
            DmpExcel dmpExcel = new DmpExcel();
            dmpExcel.ExpExcel(excStruct);
        }

        protected void fh_Click(object sender, EventArgs e)
        {
            Page.ClientScript.RegisterStartupScript(this.GetType(), "fh", "<script>location.href=('ZS_ProjectBZXXGS.aspx');</script>");
        }

        protected void cx_Click(object sender, EventArgs e)
        {
            Bind();
        }

       

   

    }
}


